package com.bening.smsapp.filechangereg;

import com.bening.smsapp.bean.FileSource;
import com.bening.smsapp.bootstrap.BootConfigParameters;
import com.bening.smsapp.constant.PropertiesConstant;
import com.bening.smsapp.constant.SmsAppConstant;
import com.bening.smsapp.factory.SmsAppFactory;
import com.bening.smsapp.util.SmsAppTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import com.bening.smsapp.bean.*;
import org.apache.log4j.Logger;

public class DataFetchProcess
{
    private static DataFetchProcess thisInstance = null;
    private static BootConfigParameters params = BootConfigParameters.getInstance();

    /**
     * prevent object instantiation outside this class.
     */
    private DataFetchProcess()
    {
    }

    /**
     * this is the only way to get the instance of this object
     * @return instance of this class
     */
    public static synchronized DataFetchProcess getInstance()
    {
        if( thisInstance == null )
        {
            thisInstance = new DataFetchProcess();
        }

        return thisInstance;
    }

    /**
     * get list of files being processed, read from the database.
     * @return list of files being processed
     */
    public List getRegisteredFilesList(MachineSource m,PathSource p)
    {
    	String sql = params.getQueryProperties().getProperty(PropertiesConstant.SQL_SELECT_FILE_EXIST);
        final Logger logger = Logger.getLogger( getClass() );
        final String METHOD_SIGNATURE = "[filechangereg]";

        Connection conn;
        List result = null;
        PreparedStatement prepStmt = null;
        ResultSet rs = null;

        if ( logger.isDebugEnabled() )
        {
            logger.debug( METHOD_SIGNATURE + "get a database connection." );
        }

        conn = SmsAppFactory.getDbConnection();
        try
        {
            prepStmt = conn.prepareStatement( sql );
            prepStmt.setString( 1,  m.getMachineId() );
            prepStmt.setString(2, p.getNodeId());
            rs = prepStmt.executeQuery();
            result = new ArrayList();

            boolean more = rs.next();
            while( more )
            {
                result.add(rs.getString("FILENAME"));
                more = rs.next();
            }
        }
        catch ( SQLException e )
        {
            logger.error( METHOD_SIGNATURE + "SQLException: error while retrieving data from database. " + e.getMessage(), e );
        }
        finally
        {
            try
            {
                if ( rs != null )
                {
                    rs.close();
                }
                else
                {
                }
                if ( prepStmt != null )
                {
                    prepStmt.close();
                }
                if ( conn != null )
                {
                    conn.close();
                }
            }
            catch ( SQLException e )
            {
                logger.error( "SQLException: " + e.getMessage(), e );
                SmsAppTools.InsertEventLog(SmsAppConstant.SQL_ERROR, e.getMessage(),p.getPathSourceId());
            }
        }


        return result;
    }


    /**
     * this method returns list of files to be downloaded.
     * list of files to be downloaded has the
     * @return list of files to be downloaded
     */
    public List getToBeDownloadedList(MachineSource m,PathSource p)
    {
        final Logger logger = Logger.getLogger( getClass() );
        final String METHOD_SIGNATURE = "[filechangereg]";

        List result = new ArrayList();
        Connection conn;
        PreparedStatement prepStmt = null;
        ResultSet rs = null;

        conn = SmsAppFactory.getDbConnection();
        try
        {
        	String sql = params.getQueryProperties().getProperty(PropertiesConstant.SQL_SELECT_REGISTERED_FILE_LIST);
            prepStmt = conn.prepareStatement(sql);
            prepStmt.setString( 1,  m.getMachineId() );
            prepStmt.setString(2, p.getNodeId());
            prepStmt.setInt( 3,  SmsAppConstant.FLAG_DOWNLOAD_READY );

            rs = prepStmt.executeQuery();

            boolean more = rs.next();
            while( more )
            {
            	FileSource f = new FileSource();
            	f.setFileName(rs.getString("FILENAME"));
            	f.setLocalFilePath(rs.getString("LOCAL_FILEPATH"));
            	f.setDownloadedTime(new Date());
                result.add(f);
                more = rs.next();
            }
        }
        catch ( SQLException e )
        {
            logger.error( METHOD_SIGNATURE + "SQLException: error while getting files to be downloaded list." );
            SmsAppTools.InsertEventLog(SmsAppConstant.SQL_ERROR, e.getMessage(),p.getPathSourceId());
        }
        finally
        {
            try
            {
                if( rs != null )
                {
                    rs.close();
                }
                if( prepStmt != null )
                {
                    prepStmt.close();
                }
                if ( conn != null )
                {
                    conn.close();
                }
            }
            catch ( SQLException e )
            {
                logger.error( "SQLException: " + e.getMessage(), e );
            }
        }

        return result;
    }

    /**
     * store the list of files to be downloaded to database.
     */
    public void storeToBeDownloadedList( List toStoreList, int storeType, int pathSource )
    {
        final Logger logger = Logger.getLogger( getClass() );
        final String METHOD_SIGNATURE = "[filechangereg]";
        final String sql = params.getQueryProperties().getProperty(PropertiesConstant.SQL_INSERT_TOBE_DOWNLOADED); 
        Connection conn;
        PreparedStatement prepStmt = null;

        if ( toStoreList != null && toStoreList.size() > 0 )
        {
            if ( logger.isDebugEnabled() )
            {
                logger.debug( METHOD_SIGNATURE + "acquiring database connection." );
            }

            conn = SmsAppFactory.getDbConnection();

            try
            {
                conn.setAutoCommit( false );

                // insert to table with flag Ready for download
                prepStmt = conn.prepareStatement( sql );
                int listSize = toStoreList.size();

                for( int i = 0; i < listSize; i++ )
                {
                    //String filename = ( String ) toStoreList.get( i );
                	FileSource f = (FileSource)toStoreList.get(i);
 
                    prepStmt.setString( 1, f.getFileName() );
                    prepStmt.setInt( 2, f.getSourcePathId() );
                    prepStmt.setDate( 3, null);
                    prepStmt.setDate( 4, null);
                    prepStmt.setDate( 5, null);
                    if(storeType == SmsAppConstant.FLAG_LOCAL_MACHINE)
                    	prepStmt.setInt( 6, SmsAppConstant.FLAG_PARSE_READY );
                    else if(storeType == SmsAppConstant.FLAG_REMOTE_MACHINE)
                    	prepStmt.setInt( 6, SmsAppConstant.FLAG_DOWNLOAD_READY );
                    prepStmt.setString(7,f.getLocalFilePath());
                    prepStmt.setLong(8,f.getFileSize());
                    prepStmt.setString(9, String.valueOf(f.getCdrType()));
                    prepStmt.execute();
                }
                conn.commit();
            }
            catch ( SQLException e )
            {
                logger.error( METHOD_SIGNATURE + "SQLException: error when inserting to database. " + e.getMessage(), e );
                SmsAppTools.InsertEventLog(SmsAppConstant.SQL_ERROR, e.getMessage(),pathSource);
            }
            finally
            {
                try
                {
                    if( prepStmt != null )
                    {
                        prepStmt.close();
                    }
                    if ( conn != null )
                    {
                        conn.close();
                    }
                }
                catch ( SQLException e )
                {
                    logger.error( METHOD_SIGNATURE + "failed in closing database connection. " + e.getClass() + ": " +
                            e.getMessage(), e );
                }
            }
        }
        else
        {
        }
    }
}
